<?php

class ReportCore {

    public static function getSalesTotal($dateBegin, $dateEnd, $getCount = false) {
        if (!AppTools::validateDate($dateBegin) || !AppTools::validateDate($dateEnd)) {
            return 0;
        }

        $where = array();

        $where[] = "t.is_income = 0";
        $where[] = "t.trans_type IN (" . implode(',', array(TransactionTable::TRANS_TYPE_CUSTOMER, TransactionTable::TRANS_TYPE_SERVICE)) . ")";
        $where[] = "t.is_active = " . TransactionTable::ACTIVE;
        $where[] = "t.trans_date BETWEEN '$dateBegin' AND '$dateEnd'";

        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        if ($getCount) {
            $sql = "SELECT COUNT(t.id) AS total
                    FROM `transaction` t
                    $where";
        } else {
            $sql = "SELECT SUM(t.total_price) AS total
                    FROM `transaction` t
                    $where";
        }

        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $rows[0]['total'];
    }

    public static function getSalesGroupedByMonths($dateBegin, $dateEnd) {
        if (!AppTools::validateDate($dateBegin) || !AppTools::validateDate($dateEnd)) {
            return 0;
        }

        $where = array();

        $where[] = "t.is_income = 0";
        $where[] = "t.trans_type IN (" . implode(',', array(TransactionTable::TRANS_TYPE_CUSTOMER, TransactionTable::TRANS_TYPE_SERVICE)) . ")";
        $where[] = "t.is_active = " . TransactionTable::ACTIVE;
        $where[] = "t.trans_date BETWEEN '$dateBegin' AND '$dateEnd'";

        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        $sql = "SELECT
                    SUM(t.total_price) AS total,
                    YEAR(t.trans_date) AS 'year',
                    MONTH(t.trans_date) AS 'month'
                FROM `transaction` t
                $where
                GROUP BY YEAR(t.trans_date), MONTH(t.trans_date)";


        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $rows;
    }

    public static function getOutcomeData($userId = 0, $storeId = 0, $transDateBegin = null, $transDateEnd = null, $categoryId = 0, $productId = 0, $sorting = 't.id ASC', $start = 0, $length = 30, $forExcel = false) {
        $userId = (int) $userId;
        $storeId = (int) $storeId;
        $categoryId = (int) $categoryId;
        $productId = (int) $productId;

        $where = array();

        $where[] = "t.is_income = 0";
        $where[] = "t.trans_type IN (" . implode(',', array(TransactionTable::TRANS_TYPE_CUSTOMER, TransactionTable::TRANS_TYPE_SERVICE)) . ")";
        $where[] = "t.is_active = " . TransactionTable::ACTIVE;
        $where[] = "ti.is_active = " . TransactionItemTable::ACTIVE;

        if ($userId) {
            $where[] = "t.user_id = $userId";
        }
        if ($storeId) {
            $where[] = "t.store_id = $storeId";
        }
        if ($categoryId) {
            $where[] = "p.category_id = $categoryId";
        }
        if ($productId) {
            $where[] = "ti.product_id = $productId";
        }
        if (AppTools::validateDate($transDateBegin)) {
            $where[] = "t.trans_date >= '$transDateBegin'";
        }
        if (AppTools::validateDate($transDateEnd)) {
            $where[] = "t.trans_date <= '$transDateEnd'";
        }
        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        $select = "t.id AS order_id,
                    u.username,
                    u.firstname,
                    s.name AS store_name,
                    t.trans_date AS order_date,
                    CASE t.trans_type
                            WHEN " . TransactionTable::TRANS_TYPE_CUSTOMER . " THEN 'Үйлчлүүлэгч'
                            WHEN " . TransactionTable::TRANS_TYPE_SERVICE . " THEN 'Гоо сайхан'
                            ELSE 0
                    END AS order_type,
                    CASE t.trans_type
                            WHEN " . TransactionTable::TRANS_TYPE_CUSTOMER . " THEN c.firstname
                            WHEN " . TransactionTable::TRANS_TYPE_SERVICE . " THEN u1.username
                            ELSE 0
                    END AS ordered_to,
                    t.total_price,
                    GROUP_CONCAT(DISTINCT CONCAT('\"', tp.payment_method_id, '\":\"', tp.amount, '\"') SEPARATOR ',') AS payments,
                    t.created_at AS order_created_at,
                    t.updated_at AS order_updated_at,
                    cat.name AS cat_name,
                    p.name AS prod_name,
                    ti.quantity * -1 AS qty,
                    ti.price";
        $joins = "LEFT JOIN `transaction_item` ti ON ti.is_active = " . TransactionItemTable::ACTIVE . " AND t.id = ti.trans_id
                    LEFT JOIN `transaction_payment` tp ON tp.is_active = " . TransactionPaymentTable::ACTIVE . " AND t.id = tp.trans_id
                    LEFT JOIN `product` p ON ti.product_id = p.id
                    LEFT JOIN `category` cat ON p.category_id = cat.id
                    LEFT JOIN `user` u ON t.user_id = u.id
                    LEFT JOIN `store` s ON t.store_id = s.id
                    LEFT JOIN `user` u1 ON t.trans_entity_id = u1.id
                    LEFT JOIN `customer` c ON t.trans_entity_id = c.id";
        $groupBy = "GROUP BY ti.id";
        $order = "ORDER BY $sorting";
        $limit = "LIMIT $start, $length";

        $pdo = Doctrine_Manager::connection()->getDbh();
        if ($forExcel) {
            $sql = "SELECT $select
                FROM `transaction` t
                $joins
                $where
                $groupBy
                $order";
            $numrows = 0;
        } else {
            $sql = "SELECT $select
                FROM `transaction` t
                $joins
                $where
                $groupBy
                $order
                $limit";

            $sqlCount = "SELECT COUNT(*) AS numrow
                    FROM `transaction` t
                    $joins
                    $where
                    $groupBy";
            $numrows = $pdo->query($sqlCount)->rowCount();
        }
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

        return array('numrows' => $numrows, 'rows' => $rows);
    }

    public static function getOutcomeDataSummary($userId = 0, $storeId = 0, $transDateBegin = null, $transDateEnd = null, $categoryId = 0, $productId = 0) {
        $userId = (int) $userId;
        $storeId = (int) $storeId;
        $categoryId = (int) $categoryId;
        $productId = (int) $productId;

        $where = array();

        $where[] = "t.is_income = 0";
        $where[] = "t.trans_type IN (" . implode(',', array(TransactionTable::TRANS_TYPE_CUSTOMER, TransactionTable::TRANS_TYPE_SERVICE)) . ")";
        $where[] = "t.is_active = " . TransactionTable::ACTIVE;
        $where[] = "ti.is_active = " . TransactionItemTable::ACTIVE;

        if ($userId) {
            $where[] = "t.user_id = $userId";
        }
        if ($storeId) {
            $where[] = "t.store_id = $storeId";
        }
        if ($categoryId) {
            $where[] = "p.category_id = $categoryId";
        }
        if ($productId) {
            $where[] = "ti.product_id = $productId";
        }
        if (AppTools::validateDate($transDateBegin)) {
            $where[] = "t.trans_date >= '$transDateBegin'";
        }
        if (AppTools::validateDate($transDateEnd)) {
            $where[] = "t.trans_date <= '$transDateEnd'";
        }
        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        $joins = "LEFT JOIN `transaction_item` ti ON ti.is_active = " . TransactionItemTable::ACTIVE . " AND t.id = ti.trans_id
                    LEFT JOIN `transaction_payment` tp ON tp.is_active = " . TransactionPaymentTable::ACTIVE . " AND t.id = tp.trans_id
                    LEFT JOIN `product` p ON ti.product_id = p.id
                    LEFT JOIN `category` cat ON p.category_id = cat.id
                    LEFT JOIN `user` u ON t.user_id = u.id
                    LEFT JOIN `store` s ON t.store_id = s.id
                    LEFT JOIN `user` u1 ON t.trans_entity_id = u1.id
                    LEFT JOIN `customer` c ON t.trans_entity_id = c.id";
        $groupBy = "GROUP BY ti.id";

        $pdo = Doctrine_Manager::connection()->getDbh();
        $sql = "SELECT
                    SUM(sub.price) AS total,
                    SUM(sub.qty) AS quantity
                FROM(SELECT 
                        ti.price AS price,
                        ti.quantity * -1 AS qty
                    FROM `transaction` t
                    $joins
                    $where
                    $groupBy
                ) AS sub";

        $total = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $total[0];
    }

}

?>